import xlrd
import xlwt
import openpyxl
from xlutils.copy import copy


class Excel:
    def __init__(self, filename, mode='r'):
        ##'''
        ##para:
        ##filename:文件名
        ##mode:默认值为'r'
        ##	'r':只读文件
        ##	'r+':修改原有文件
        ##	'w':创建一个新文件
        ##'''
        self.filename = filename
        if mode == 'r':
            self.mode = 'r'
            if filename.endswith("xls"):
                self.workbook = xlrd.open_workbook(filename)
                self.sheetnames = self.workbook.sheet_names()
                self.nsheets = self.workbook.nsheets
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = [self.workbook.sheet_by_name(name) for name in self.sheetnames]
                self.nrows = [sheet.nrows for sheet in self.sheets]
                self.ncols = [sheet.ncols for sheet in self.sheets]
            elif filename.endswith("xlsx"):
                self.workbook = openpyxl.load_workbook(filename)
                self.sheetnames = self.workbook.sheetnames
                self.nsheets = len(self.sheetnames)
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook.worksheets
                self.nrows = [sheet.max_row for sheet in self.sheets]
                self.ncols = [sheet.max_column for sheet in self.sheets]
        elif mode == 'w':
            self.mode = 'w'
            if filename.endswith('xlsx'):
                self.workbook = openpyxl.Workbook()
                self.sheetnames = self.workbook.sheetnames
                self.nsheets = 1
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook.worksheets
            elif filename.endswith('xls'):
                self.workbook = xlwt.Workbook()
                self.init = self.workbook.add_sheet("Sheet")  # temp attribute
                self.sheetnames = ["Sheet"]
                self.nsheets = 1
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook._Workbook__worksheets
        elif mode == 'r+':
            self.mode = 'r+'
            if filename.endswith('xlsx'):
                self.ini_workbook = openpyxl.load_workbook(filename)
                self.workbook = openpyxl.load_workbook(filename)
                self.sheetnames = self.workbook.sheetnames
                self.nsheets = len(self.sheetnames)
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.ini_sheetname_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook.worksheets
                self.ini_sheets = self.ini_workbook.worksheets
                self.nrows = [sheet.max_row for sheet in self.ini_sheets]
                self.ncols = [sheet.max_column for sheet in self.ini_sheets]
            elif filename.endswith('xls'):
                self.ini_workbook = xlrd.open_workbook(filename)
                self.workbook = copy(self.ini_workbook)
                self.sheetnames = self.ini_workbook.sheet_names()
                self.nsheets = self.ini_workbook.nsheets
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.ini_sheetname_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook._Workbook__worksheets
                self.ini_sheets = [self.ini_workbook.sheet_by_name(name) for name in self.sheetnames]
                self.nrows = [sheet.nrows for sheet in self.ini_sheets]
                self.ncols = [sheet.ncols for sheet in self.ini_sheets]

    def get_nrows(self, sheetindex=None, sheetname=None):
        ##'''
        ##返回某sheet的行数
        ##'''
        if self.mode == "r":
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            nrows = self.nrows[sheetindex]
            return nrows
        if self.mode == "r+":
            if sheetname:
                sheetindex = self.ini_sheetnames_with_indexs[sheetname]
            nrows = self.nrows[sheetindex]
            return nrows
        return "get_nrows方法为'r'和'r+'模式特有！"

    def get_ncols(self, sheetindex=None, sheetname=None):
        ##'''
        ##返回某sheet的列数
        ##'''
        if self.mode == "r":
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            ncols = self.ncols[sheetindex]
            return ncols
        if self.mode == "r+":
            if sheetname:
                sheetindex = self.ini_sheetnames_with_indexs[sheetname]
            ncols = self.ncols[sheetindex]
            return ncols
        return "get_ncols方法为'r'和'r+'模式特有！"

    def get_cell_value(self, row, col, sheetindex=None, sheetname=None):
        ##'''
        ##返回某sheet中某单元格的值，返回值类型为str、int或float等
        ##'''
        if self.mode == 'r':
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                value = self.sheets[sheetindex].cell(row, col).value
            elif self.filename.endswith("xlsx"):
                value = self.sheets[sheetindex].cell(row + 1, col + 1).value
            return value
        if self.mode == 'r+':
            if sheetname:
                sheetindex = self.ini_sheetname_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                value = self.ini_sheets[sheetindex].cell(row, col).value
            elif self.filename.endswith("xlsx"):
                value = self.ini_sheets[sheetindex].cell(row + 1, col + 1).value
            return value
        return "get_cell_value方法为'r'和'r+'模式特有！"

    def get_row_values(self, row, sheetindex=None, sheetname=None):
        ##'''
        ##返回某sheet中某行的值，返回一个值列表
        ##'''
        if self.mode == 'r':
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                row_values_lst = self.sheets[sheetindex].row_values(row)
            elif self.filename.endswith("xlsx"):
                row_values_lst = []
                for col in range(self.get_ncols(sheetindex=sheetindex, sheetname=sheetname)):
                    row_values_lst.append(self.sheets[sheetindex].cell(row + 1, col + 1).value)
            return row_values_lst
        if self.mode == 'r+':
            if sheetname:
                sheetindex = self.ini_sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                row_values_lst = self.ini_sheets[sheetindex].row_values(row)
            elif self.filename.endswith("xlsx"):
                row_values_lst = []
                for col in range(self.get_ncols(sheetindex=sheetindex, sheetname=sheetname)):
                    row_values_lst.append(self.ini_sheets[sheetindex].cell(row + 1, col + 1).value)
            return row_values_lst
        return "get_row_values方法为'r'和'r+'模式特有！"

    def get_col_values(self, col, sheetindex=None, sheetname=None):
        ##'''
        ##返回某sheet中某列的值，返回一个值列表
        ##'''
        if self.mode == 'r':
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                col_values_lst = self.sheets[sheetindex].col_values(col)
            elif self.filename.endswith("xlsx"):
                col_values_lst = []
                for row in range(self.get_nrows(sheetindex=sheetindex, sheetname=sheetname)):
                    col_values_lst.append(self.sheets[sheetindex].cell(row + 1, col + 1).value)
            return col_values_lst
        if self.mode == 'r+':
            if sheetname:
                sheetindex = self.ini_sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xls"):
                col_values_lst = self.ini_sheets[sheetindex].col_values(col)
            elif self.filename.endswith("xlsx"):
                col_values_lst = []
                for row in range(self.get_nrows(sheetindex=sheetindex, sheetname=sheetname)):
                    col_values_lst.append(self.ini_sheets[sheetindex].cell(row + 1, col + 1).value)
            return col_values_lst
        return "get_col_values方法为'r'和'r+'模式特有！"

    def write_cell_value(self, row, col, value, sheetindex=None, sheetname=None):
        ##'''
        ##向某sheet中的某单元格写入值
        ##'''
        if self.mode == "w" or self.mode == "r+":
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xlsx"):
                self.sheets[sheetindex].cell(row + 1, col + 1, value)
                self.sheets = self.workbook.worksheets
            elif self.filename.endswith("xls"):
                self.sheets[sheetindex].write(row, col, value)
                self.sheets = self.workbook._Workbook__worksheets
            return None
        return "'r'模式下无write_cell_value方法！"

    def write_values(self, coordinate, values_lst, direction, sheetindex=None, sheetname=None):
        ##'''
        ##给一个值列表，从某sheet的某个单元格坐标(coordinate)开始，竖直向下(direction="v")或水平向右(direction="h")依次向单元格写入值
        ##'''
        if self.mode == "w" or self.mode == "r+":
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if direction == "h":
                if self.filename.endswith("xlsx"):
                    for col, value in enumerate(values_lst):
                        self.sheets[sheetindex].cell(coordinate[0] + 1, coordinate[1] + 1 + col, value)
                    self.sheets = self.workbook.worksheets
                elif self.filename.endswith("xls"):
                    for col, value in enumerate(values_lst):
                        self.sheets[sheetindex].write(coordinate[0], coordinate[1] + col, value)
                    self.sheets = self.workbook._Workbook__worksheets
            elif direction == "v":
                if self.filename.endswith("xlsx"):
                    for row, value in enumerate(values_lst):
                        self.sheets[sheetindex].cell(coordinate[0] + 1 + row, coordinate[1] + 1, value)
                    self.sheets = self.workbook.worksheets
                elif self.filename.endswith("xls"):
                    for row, value in enumerate(values_lst):
                        self.sheets[sheetindex].write(coordinate[0] + row, coordinate[1], value)
                    self.sheets = self.workbook._Workbook__worksheets
            return None
        return "'r'模式下无write_values方法！"

    def create_sheet(self, sheetname, insertindex=None):
        ##'''
        ##创建一张新的sheet,insertindex表示插入的位置索引，如为空则默认插在最后
        ##'''
        if self.mode == "w" or self.mode == "r+":
            if self.filename.endswith("xlsx"):
                if insertindex == None:
                    self.workbook.create_sheet(sheetname)
                    self.sheetnames = self.workbook.sheetnames
                    self.nsheets += 1
                    self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                    self.sheets = self.workbook.worksheets
                else:
                    self.workbook.create_sheet(title=sheetname, index=insertindex)
                    self.sheetnames = self.workbook.sheetnames
                    self.nsheets += 1
                    self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                    self.sheets = self.workbook.worksheets
            elif self.filename.endswith("xls"):
                if insertindex == None:
                    self.workbook.add_sheet(sheetname)
                    self.sheetnames.append(sheetname)
                    self.nsheets += 1
                    self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                    self.sheets = self.workbook._Workbook__worksheets
                else:
                    self.workbook.add_sheet(sheetname)
                    temp = self.workbook._Workbook__worksheets[-1]
                    del self.workbook._Workbook__worksheets[-1]
                    self.workbook._Workbook__worksheets.insert(insertindex, temp)
                    self.sheetnames.insert(insertindex, sheetname)
                    self.nsheets += 1
                    self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                    self.sheets = self.workbook._Workbook__worksheets
            return None
        return "'r'模式下无create_sheet方法！"

    def remove_sheet(self, sheetindex=None, sheetname=None):
        ##'''
        ##删除某张sheet
        ##'''
        if self.mode == "w" or self.mode == "r+":
            if sheetname:
                sheetindex = self.sheetnames_with_indexs[sheetname]
            if self.filename.endswith("xlsx"):
                del self.workbook[self.sheetnames[sheetindex]]
                self.sheetnames = self.workbook.sheetnames
                self.nsheets -= 1
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook.worksheets
            elif self.filename.endswith("xls"):
                self.workbook._Workbook__worksheets = [worksheet for worksheet in self.workbook._Workbook__worksheets if
                                                       worksheet.name != self.sheetnames[sheetindex]]
                self.sheetnames = [sheetname for sheetname in self.sheetnames if
                                   sheetname != self.sheetnames[sheetindex]]
                self.nsheets -= 1
                self.sheetnames_with_indexs = {sheet: index for index, sheet in enumerate(self.sheetnames)}
                self.sheets = self.workbook._Workbook__worksheets
            return None
        return "'r'模式下无remove_sheet方法！"

    def save_file(self, filename=None):
        ##'''
        ##保存Excel
        ##'''
        if self.mode == "r":
            return "'r'模式下无save_file方法！"
        elif filename != None:
            self.workbook.save(filename)
        elif filename == None:
            self.workbook.save(self.filename)
        return None

# if __name__ == "__main__":
##		exl = Excel("test11.xls","r+")
##		a = exl.get_nrows(sheetindex=0)
##		b = exl.get_ncols(sheetindex=0)
##		c = exl.get_cell_value(0,0,sheetindex=0)
##		d = exl.get_col_values(1,sheetindex=0)
##		e = exl.get_row_values(1,sheetindex=0)
##		f = exl.create_sheet("shenwei1",insertindex=0)
##		exl.create_sheet("myy",insertindex=0)
##		g = exl.write_values((1,2),[1,2,3,"",6],"v",sheetindex=0)
##		exl.save_file("test11.xls")
